Walkthrough: Yearly Sales Variance

Create a PQL formula to add to queries to see the yearly sales variance.

  • Click here to review PQL functions.

Formula Syntax

The syntax of the formula that you are about to create will be:

([measures].[data Sales])-([measures].[data Sales],PrevMember([data].[dateKey year]))

Build your Formula

Step 1: Subtract sales from sales

Build a custom member in Formula, using two data points:

  1. Drag a Data Point onto the canvas from the Elements list (purple arrow) and select the Sales measure for it (blue arrow).

  2. Type the minus sign and then add the second data point with the same Sales measure.

Step 2: Choose the "previous year" for the second measure

To create the calculation, you need to make the second data point represent the sales from the previous year.

With the second data point selected on the canvas:

  1. Select the dateKey Year hierarchy in the Select Hierarchy panel.

  2. Instead of selecting All or a year from the Elements panel, click the Formula icon next to the search tool (orange arrow above).

    3. The Select Elements panel is updated to show different options:

  3. Select Previous Member from the Elements panel (orange arrow above).

Step 3: Preview and Save

Once you are happy with your calculation:

  1. Click the Pyramid button to preview the formula syntax (orange arrow below).

  2. Then save the custom member (blue arrow).

Use your Custom Member in a discovery

To check that your calculation is behaving as expected:

  1. Create a New Discovery.

    2. You can click the Quick Discovery button in the overhead ribbon to create a new discovery containing your new calculation.

  2. In the new discovery, add: dateKey Year and Sales. Your discovery should now contain rows for each year, a Sales column, and a column for the new custom member.